package com.slofzx.nuoda.evaluation.project.util;

import com.slofzx.nuoda.common.exception.ServiceException;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.XSSFCellStyle;

import java.util.HashMap;
import java.util.Map;


/**
 * 基于poi的excel操作类
 * 支持跨文档的复制功能包含所有文字、字体、样式等等
 * 提供了sheet页复制
 * 行复制
 * 单元格复制
 * 单元格样式复制
 */
public class ExcelOperateUtil {

    /**
     * 复制sheet页
     *
     * @param fromWorkBook
     * @param toWorkBook
     * @param toSheet
     * @param fromSheet
     */
    public static void copySheet(Workbook fromWorkBook, Workbook toWorkBook, Sheet fromSheet, Sheet toSheet, Boolean isSameAllCellStyle) {
        if (fromSheet == null) {
            throw new ServiceException("来源sheet页不允许为空", "来源sheet页不允许为空", null);
        }
        if (toSheet == null) {
            throw new ServiceException("新sheet页不允许为空", "新sheet页不允许为空", null);
        }
        int firstRow = fromSheet.getFirstRowNum();
        int lastRow = fromSheet.getLastRowNum();
        for (int i = firstRow; i <= lastRow; i++) {
            // 创建新建excel Sheet的行
            Row toRow = toSheet.createRow(i);
            // 取得源有excel Sheet的行
            Row fromRow = fromSheet.getRow(i);
            if (fromRow != null) {
                // 复制行
                copyRow(fromWorkBook, toWorkBook, fromRow, toRow);
            }
        }
        // 复制列宽
        copyColWidth(fromSheet, toSheet);
        // 合并单元格
        copyMergerRegion(toSheet, fromSheet);

    }

    /**
     * 复制行
     *
     * @param fromWorkBook
     * @param toWorkBook
     * @param fromRow
     * @param toRow
     */
    public static void copyRow(Workbook fromWorkBook, Workbook toWorkBook, Row fromRow, Row toRow) {
        int firstCell = fromRow.getFirstCellNum();
        int lastCell = fromRow.getLastCellNum();
        // 设置行高
        fromRow.setHeightInPoints(fromRow.getHeightInPoints());
        for (int j = firstCell; j < lastCell; j++) {
            toRow.createCell(j);
            final Cell cell = fromRow.getCell(j);
            final Cell creatCell = toRow.getCell(j);
            if (cell != null) {
                // 复制单元格
                copyCell(fromWorkBook, toWorkBook, cell, creatCell);
            }
        }
    }

    /**
     * 复制单元格
     *
     * @param fromWorkBook
     * @param toWorkBook
     * @param fromCell
     * @param toCell
     */
    public static void copyCell(Workbook fromWorkBook, Workbook toWorkBook, Cell fromCell, Cell toCell) {
        // 设置单元格数据
        copyCellValue(fromCell, toCell);
        // 复制样式
        copyCellStyle(fromWorkBook, toWorkBook, fromCell, toCell);
    }

    /**
     * 复制单元格样式
     * 如果有大量重复的样式需要复制会创建大量CellStyle对象
     * 建议使用getCopyCellStyle方法提高性能
     *
     * @param fromWorkBook 源文档
     * @param toWorkBook   新文档
     * @param fromCell     源单元格
     * @param toCell       新单元格
     * @return
     */
    public static void copyCellStyle(Workbook fromWorkBook, Workbook toWorkBook, Cell fromCell, Cell toCell) {
        final CellStyle fromStyle = fromCell.getCellStyle();

//        CellStyle toStyle = toWorkBook.createCellStyle();
//        toStyle.cloneStyleFrom(fromStyle);
        CellStyle toStyle = getCopyCellStyle(fromWorkBook, toWorkBook, fromStyle);
        toCell.setCellStyle(toStyle);
    }

    /**
     * 通过一个单元格样式复制出一个样式并返回
     *
     * @param fromWorkBook
     * @param toWorkBook
     * @param fromStyle
     * @return
     */
    public static CellStyle getCopyCellStyle(Workbook fromWorkBook, Workbook toWorkBook, CellStyle fromStyle) {
        CellStyle toStyle = toWorkBook.createCellStyle();
        toStyle.cloneStyleFrom(fromStyle);
       // toStyle.setDataFormat(fromStyle.getDataFormat());
        //水平垂直对齐方式
//        toStyle.setAlignment(fromStyle.getAlignment());
//        toStyle.setVerticalAlignment(fromStyle.getVerticalAlignment());
//        //边框和边框颜色
//        toStyle.setBorderBottom(fromStyle.getBorderBottom());
//        toStyle.setBorderLeft(fromStyle.getBorderLeft());
//        toStyle.setBorderRight(fromStyle.getBorderRight());
//        toStyle.setBorderTop(fromStyle.getBorderTop());
//        toStyle.setTopBorderColor(fromStyle.getTopBorderColor());
//        toStyle.setBottomBorderColor(fromStyle.getBottomBorderColor());
//        toStyle.setRightBorderColor(fromStyle.getRightBorderColor());
//        toStyle.setLeftBorderColor(fromStyle.getLeftBorderColor());
//        // 设置数据格式
//        toStyle.setDataFormat(fromStyle.getDataFormat());
//        // 设置背景色
//        toStyle.setFillPattern(fromStyle.getFillPattern());
//        toStyle.setFillBackgroundColor(fromStyle.getFillBackgroundColor());
//        toStyle.setFillForegroundColor(fromStyle.getFillForegroundColor());
//        // 设置字体
//        if (fromStyle instanceof XSSFCellStyle) {
//            final Font fromFont = ((XSSFCellStyle) fromStyle).getFont();
//            final Font font = copyFont(toWorkBook, fromFont);
//            toStyle.setFont(font);
//        } else if (fromStyle instanceof HSSFCellStyle) {
//            toStyle.setFont(((HSSFCellStyle) fromStyle).getFont(fromWorkBook));
//        }
//        // 设置自动换行
//        toStyle.setWrapText(fromStyle.getWrapText());
        return toStyle;
    }

    /**
     * 复制字体
     *
     * @param toWorkBook
     * @param fromFont
     * @return
     */
    private static Font copyFont(Workbook toWorkBook, Font fromFont) {
        final Font font = toWorkBook.createFont();
        // 设置字体
        font.setFontName(fromFont.getFontName());
        // 设置字的颜色
        font.setColor(fromFont.getColor());
        // 是否加粗
        font.setBold(fromFont.getBold());
        // 字号
        font.setFontHeightInPoints(fromFont.getFontHeightInPoints());
        // 斜体
        font.setItalic(fromFont.getItalic());
        return font;
    }

    /**
     * 　 * 复制原有sheet的合并单元格到新创建的sheet
     * 　 *
     * 　 * @param sheetCreat
     * 　 *　　　　　 新创建sheet
     * 　 * @param sheet
     * 　 *　　　　　 原有的sheet
     *
     */
    public static void copyMergerRegion(Sheet sheetCreat, Sheet sheet) {
        int sheetMergerCount = sheet.getNumMergedRegions();
        for (int i = 0; i < sheetMergerCount; i++) {
            final CellRangeAddress cellAddresses = sheet.getMergedRegion(i);
            sheetCreat.addMergedRegion(cellAddresses);
        }

    }

    /**
     * 复制列宽
     */
    public static void copyColWidth(Sheet fromSheet, Sheet toSheet) {
        // 注意row0 的getLastCellNum();可能会不准确
        int lastColIndex = fromSheet.getRow(0).getLastCellNum();

        for (int i = 0; i <= lastColIndex; i++) {
            toSheet.setColumnWidth(i, fromSheet.getColumnWidth(i));
        }
    }

    /**
     * 复制单元格数据
     *
     * @param fromCell
     * @param toCell
     */
    public static void copyCellValue(Cell fromCell, Cell toCell) {
        switch (fromCell.getCellType()) {
            case NUMERIC:
                if (DateUtil.isCellDateFormatted(fromCell)) {
                    toCell.setCellValue(fromCell.getDateCellValue());
                } else {
                    toCell.setCellValue(fromCell.getNumericCellValue());
                }
                return;
            case BOOLEAN:
                toCell.setCellValue(fromCell.getBooleanCellValue());
                return;
            case ERROR:
                toCell.setCellValue(fromCell.getErrorCellValue());
                return;
            case FORMULA:
                toCell.setCellFormula(fromCell.getCellFormula());
                return;
            case STRING:
            default:
                toCell.setCellValue(fromCell.getStringCellValue());
                return;
        }
    }
//
//    public static void main(String[] args) {
//        try {
//            FileInputStream inputStream = new FileInputStream(new File("C:\\Users\\think book\\Desktop\\批量导出模板.xlsx"));
//            final Workbook workbook = WorkbookFactory.create(inputStream);
//            final Sheet fromSheet = workbook.getSheetAt(0);
//            final Sheet sheet = workbook.createSheet();
//            ExcelOperateUtil.copySheet(workbook,workbook,fromSheet,sheet);
//            OutputStream outputStream = new FileOutputStream(new File("D:\\result.xlsx"));
//            workbook.write(outputStream);
//            workbook.close();
//
//        } catch (Exception e) {
//            e.printStackTrace();
//        }
//
//    }

}
